#region License
/* Copyright (c) 2011 Maksymilian Mulawa
	
Redistribution and use in source and binary forms, with or without modification, 
 * are permitted provided that the following conditions are met:

   1. Redistributions of source code must retain the above copyright notice, 
 * this list of conditions and the following disclaimer.
   2. Redistributions in binary form must reproduce the above copyright notice, 
 * this list of conditions and the following disclaimer in the documentation 
 * and/or other materials provided with the distribution.
   3. The name of the author may not be used to endorse or promote products 
 * derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED 
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
 * AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL 
 * THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
 * OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE 
 * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF 
 * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using log4net;
using ReplicationExplorer.DomainModel.Distributor.Entities;
using ReplicationExplorer.DomainModel.Infrastructure;
using ReplicationExplorer.DomainModel.Publisher.Entities;
using ReplicationExplorer.DomainModel.Publisher.Repositories;

namespace ReplicationExplorer.Repositories
{
    public class PublisherRepository : IPublisherRepository
    {
        private static readonly ILog _log = LogManager.GetLogger(typeof(PublisherRepository));

        private readonly IDistributorSession _distributorSession;

        public PublisherRepository(IDistributorSession distributorSession)
        {
            _distributorSession = distributorSession;
        }

        public List<PublisherServer> GetPublisherServers(DistributionDatabase database)
        {
            List<PublisherServer> publishers = new List<PublisherServer>();
            try
            {
                using (SqlConnection connection = new SqlConnection(_distributorSession.GetDistributionDatabaseConnectionString(database.Name)))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(ReplicationExplorerSqlQueries.sp_helpdistpublisher, connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = _distributorSession.SqlQueryTimeoutInSeconds;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if(((string)reader["distribution_db"]).Equals(database.Name, StringComparison.InvariantCultureIgnoreCase))
                            {
                                PublisherServer publisherServer = new PublisherServer();
                                publisherServer.DistributionDatabase = database;
                                publisherServer.DistributorSession = _distributorSession;
                                publisherServer.Name = (string) reader["name"];
                                publisherServer.SnapshotFolder = (string)reader["working_directory"];
                                publisherServer.PublisherType = (string)reader["publisher_type"];
                                publisherServer.IsActive = (bool)reader["active"];
                                if(publisherServer.IsActive)
                                {
                                    SqlCommand cmdActivePublisher = new SqlCommand("sp_replmonitorhelppublisher", connection);
                                    cmdActivePublisher.CommandType = CommandType.StoredProcedure;
                                    cmdActivePublisher.CommandTimeout = _distributorSession.SqlQueryTimeoutInSeconds;
                                    cmdActivePublisher.Parameters.AddWithValue("@publisher", publisherServer.Name);
                                    using (SqlDataReader activePublisherReader = cmdActivePublisher.ExecuteReader())
                                    {
                                        while (activePublisherReader.Read())
                                        {
                                            //Check http://msdn.microsoft.com/en-us/library/ms174423.aspx
                                            publisherServer.Status = (int)activePublisherReader["status"];
                                            publisherServer.Warning = (int)activePublisherReader["warning"];
                                            publisherServer.PublicationCount = (int)activePublisherReader["publicationcount"];
                                        }
                                    }
                                }

                                publishers.Add(publisherServer);
                            }
                        }
                    }
                
                }
            }
            catch (Exception e)
            {
                _log.Error(e);
            }

            return publishers;
        }

        public List<Publication> GetPublications(PublisherServer publisherServer)
        {
            List<Publication> publications = new List<Publication>();
            try
            {
                string distributionDatabaseConnectionString = _distributorSession.GetDistributionDatabaseConnectionString(publisherServer.DistributionDatabase.Name);
                using (SqlConnection connection = new SqlConnection(distributionDatabaseConnectionString))
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand(ReplicationExplorerSqlQueries.sp_replmonitorhelppublication, connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = _distributorSession.SqlQueryTimeoutInSeconds;
                    cmd.Parameters.AddWithValue("@publisher", publisherServer.Name);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Publication publication = new Publication();
                            publication.PublisherServer = publisherServer;
                            publication.DistributorSession = _distributorSession;
                            publication.Id = (int) reader["publication_id"];
                            publication.Name = (string)reader["publication"];
                            publication.PublicationDatabase = (string)reader["publisher_db"];
                            publication.PublicationType = (int)reader["publication_type"];
                            publication.Status = (int)reader["status"];
                            publication.Warning = (int)reader["warning"];
                            //All three latency figures can be NULL
                            publication.WorstLatencyInSeconds =  DataReaderHelper.ReadInt(reader, "worst_latency");
                            publication.BestLatencyInSeconds = DataReaderHelper.ReadInt(reader,"best_latency");
                            publication.AvarageLatencyInSeconds = DataReaderHelper.ReadInt(reader,"average_latency");

                            publication.LastDistributorAgentRun = DataReaderHelper.ReadDateTime(reader,"last_distsync");
                            publication.PublicationRetensionPeriodInHours = (int)reader["retention"];

                            publication.LatencyThreshold = DataReaderHelper.ReadInt(reader, "latencythreshold");
                            publication.ExpirationThreshold = DataReaderHelper.ReadInt(reader, "expirationthreshold");

                            publication.SubscriptionCount = (int) reader["subscriptioncount"];
                            //publication.DistributionAgentCount = (int) reader["runningdistagentcount"];

                            publication.SnapshotAgentName = DataReaderHelper.ReadStrting(reader,"snapshot_agentname");
                            publication.LogReaderAgentName = DataReaderHelper.ReadStrting(reader, "logreader_agentname");
                            publication.QueueReaderAgentName = DataReaderHelper.ReadStrting(reader, "qreader_agentname");

                            try
                            {
                                SqlCommand cmdMSPublications = new SqlCommand(ReplicationExplorerSqlQueries.SelectPublicationsProperties, connection);
                                cmdMSPublications.CommandTimeout = _distributorSession.SqlQueryTimeoutInSeconds;
                                cmdMSPublications.Parameters.AddWithValue("@publicationId", publication.Id);
                                cmdMSPublications.Parameters.AddWithValue("@publicationDatabase", publication.PublicationDatabase);
                                cmdMSPublications.Parameters.AddWithValue("@publicationName", publication.Name);

                                using (SqlDataReader msPublicationsReader = cmdMSPublications.ExecuteReader())
                                {
                                    if (msPublicationsReader.Read())
                                    {
                                        publication.AllowPushSubscription = (bool)msPublicationsReader["allow_push"];
                                        publication.AllowPullSubscription = (bool)msPublicationsReader["allow_pull"];
                                        publication.AllowAnonymousSubscription = (bool)msPublicationsReader["allow_anonymous"];
                                        publication.Description = DataReaderHelper.ReadStrting(msPublicationsReader,"description");
                                        publication.SynchronizationMethod = (int)msPublicationsReader["sync_method"];
                                        publication.AllowSubscriptionDatabaseCopy = (bool)msPublicationsReader["allow_subscription_copy"];
                                        publication.AllowQueuedUpdates = (bool)msPublicationsReader["allow_queued_tran"];
                                        publication.AllowSubscriptionInitializeFromBackup = (bool) msPublicationsReader["allow_initialize_from_backup"];
                                        publication.ImmediateSync = (bool) msPublicationsReader["immediate_sync"];
                                    }
                                }
                            }
                            catch (Exception e)
                            {
                                //Missing permissions to MSpublications table
                                _log.Warn("Error could be caused by missing permissions to MSpublications table", e);
                            }
                       
                            publications.Add(publication);
                        }
                    }

                }
            }
            catch (Exception e)
            {
                _log.Error(e);
                throw;
            }

            return publications;

        }
    }
}